import numpy as np
import pandas as pd

from services.query_mysql import query_mysql as qm
class aj_info:
    def __init__(self,dbname,strWhere):
        self.db_name = dbname
        self.strWhere=strWhere
    #案件基本信息
    def ajinfo(self):
        q=qm("select * from(SELECT a.AJID,a.AJNO, a.OneType, a.TwoType,a.StartTime,a.EndTime,substring_index(substring_index(a.syr,',',b.help_topic_id + 1	),',',	- 1) AS syr, a.shr,a.Address,a.Money,a.url,a.syrNoType,a.syrNo,a.IMTYPE,a.IM, a.AJInfo FROM aj_info a INNER JOIN mysql.help_topic b ON b.help_topic_id < (length(a.syr) - length(REPLACE(a.syr, ',', '')) + 1)WHERE	LEFT (a.StartTime, 7) > '2020-01') b where b.syr not in ('0%','1%')",self.db_name)
        col = ['AJID', 'AJNO', 'OneType', 'TwoType', 'StartTime', 'EndTime','syr','shr','Address','Money','url','syrNoType','syrNo','IMTYPE','IM','AJInfo']
        return pd.DataFrame(np.array(q.query_list()), columns=col)

    #涉案号码
    def sahm(self):
        q=qm("select s.`涉案电话` ,right(s.`发案时间`,19) '发案开始时间', left (s.`发案时间`,19) '发案结束时间'  from sc_st_aj s",self.db_name)
        for i in q.query_list():
            print(i)
    #受害人号码
    def shrhm(self):
        q = qm("select s.`受害人电话`,right(s.`发案时间`,19) '发案开始时间', left (s.`发案时间`,19) '发案结束时间', from sc_st_aj s",
               self.db_name)
        for i in q.query_list():
            print(i)
    #简要案情
    def jyaq(self):
        q = qm("SELECT s.`简要案情` FROM sc_st_aj s",
               self.db_name)
        for i in q.query_list():
            print(i)
    #受害人被叫
    def shrbj(self):
        if self.strWhere==0:
            strsql="select * from shrbj"
        else:
            strsql="select * from shrbj " + self.strWhere
        #print(strsql)
        q = qm(strsql, self.db_name)
        for i in q.query_list():
           print(i)
    #受害人主叫
    def shrzj(self):
        q=qm("select * from xyrzj",self.db_name)
        for i in q.query_list():
            print(i)
    #嫌疑人被叫
    def xyrbj(self):
        q=qm("select * from xyrbj",self.db_name)
        for i in q.query_list():
            print(i)
    #嫌疑人主叫
    def xyrzj(self):
        q = qm("select * from xyrzj", self.db_name)
        for i in q.query_list():
            print(i)

